import logging
import math
from typing import Optional, List

from fastapi import APIRouter, Depends, Query, Path
from pydantic import Field, BaseModel
from sqlalchemy import text
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.orm import Session
# from fastapi import Request
from commons.BaseResponse import BaseResponse
from commons.PaginationBase import PaginationBase
from datebase import get_db
from utils.EncryptionUtil import oauth2_scheme, EncryptionMiddleware

take_out_head_router = APIRouter(prefix="/issue/takeoutHead", tags=["设备领用管理"],
                                 dependencies=[Depends(oauth2_scheme)])


class EquipmentTakeoutSearch(PaginationBase):
    # 筛选条件
    fsiteNo: Optional[str] = Field("", description="站点编号")
    ftakedateStart: Optional[str] = Field("", description="领用开始日期(yyyyMMdd)")
    ftakedateEnd: Optional[str] = Field("", description="领用结束日期(yyyyMMdd)")
    fpointNo: Optional[str] = Field("", description="点位编号")
    fbillno: Optional[str] = Field("", description="单据编号（支持前缀匹配）")
    ftakeuser: Optional[str] = Field("", description="领用人")
    fbillstate: Optional[str] = Field("", description="单据状态编码")


@take_out_head_router.get("/list", summary="设备领用查询接口")
def list_take_out(search: EquipmentTakeoutSearch = Query(), db: Session = Depends(get_db)):
    param = {}
    sql = "select count(1) from lims.s_tc_takeout_head where 1=1"
    if search.fsiteNo != "":
        sql = sql + " and fsite_no = :site_no "
        param["site_no"] = search.fsiteNo
    if search.fpointNo != "":
        sql = sql + " and fpoint_no = :fpointNo "
        param["fpointNo"] = search.fpointNo
    if search.fbillno != "":
        sql = sql + " and fbillno like concat('%', :fbillno ,'%')"
        param["fbillno"] = search.fbillno
    if search.ftakeuser != "":
        sql = sql + " and ftakeuser = :ftakeuser "
        param["ftakeuser"] = search.ftakeuser
    if search.fbillstate != "":
        sql = sql + " and fbillstate = :fbillstate "
        param["fbillstate"] = search.fbillstate
    if (search.ftakedateStart != "") and (search.ftakedateEnd != ""):
        sql = sql + " and to_char(ftakedate,'yyyymmdd') between :ftakedateStart  and :ftakedateEnd "
        param["ftakedateStart"] = search.ftakedateStart
        param["ftakedateEnd"] = search.ftakedateEnd
    count = db.execute(text(sql), param).scalar()

    param = {}
    sql = "select fbillno,fsite_no,ftakeuser,ftakedate,fpoint_no,fitemcount,fpiececount,ftotalamt,"
    sql = sql + " fexplanation,fupddate,foperator,fcheckdate,fcheckuser,fbillstate,fbilltype"
    sql = sql + " from lims.s_tc_takeout_head where 1=1"
    if search.fsiteNo != "":
        sql = sql + " and fsite_no = :site_no "
        param["site_no"] = search.fsiteNo
    if search.fpointNo != "":
        sql = sql + " and fpoint_no = :fpointNo "
        param["fpointNo"] = search.fpointNo
    if search.fbillno != "":
        sql = sql + " and fbillno like concat('%', :fbillno ,'%')"
        param["fbillno"] = search.fbillno
    if search.ftakeuser != "":
        sql = sql + " and ftakeuser = :ftakeuser "
        param["ftakeuser"] = search.ftakeuser
    if search.fbillstate != "":
        sql = sql + " and fbillstate = :fbillstate "
        param["fbillstate"] = search.fbillstate
    if (search.ftakedateStart != "") and (search.ftakedateEnd != ""):
        sql = sql + " and to_char(ftakedate,'yyyymmdd') between :ftakedateStart  and :ftakedateEnd "
        param["ftakedateStart"] = search.ftakedateStart
        param["ftakedateEnd"] = search.ftakedateEnd
    if search.order == "desc":
        sql = sql + " order by  ftakedate,fbillno desc "
    else:
        sql = sql + " order by  ftakedate,fbillno "
    sql = sql + " limit :limit offset :offset "
    param["limit"] = search.limit
    param["offset"] = search.offset
    data = db.execute(text(sql), param).fetchall()

    # details 明细列表
    param = {}
    sql_3 = " select b.fbillno,b.fline,b.fpoint_size,b.fequipmentbarcode,b.feuipmentid,b.fmacno,"
    sql_3 = sql_3 + " b.fequipmenttag,b.flinkmac,b.flinkmark,b.fbuyprice"
    sql_3 = sql_3 + " from lims.s_tc_takeout_head a,lims.s_tc_takeout_detail b where a.fbillno=b.fbillno "
    if search.fsiteNo != "":
        sql_3 = sql_3 + " and fsite_no = :site_no "
        param["site_no"] = search.fsiteNo
    if search.fpointNo != "":
        sql_3 = sql_3 + " and fpoint_no = :fpointNo "
        param["fpointNo"] = search.fpointNo
    if search.fbillno != "":
        sql_3 = sql_3 + " and a.fbillno like concat('%', :fbillno ,'%')"
        param["fbillno"] = search.fbillno
    if search.ftakeuser != "":
        sql_3 = sql_3 + " and ftakeuser = :ftakeuser "
        param["ftakeuser"] = search.ftakeuser
    if search.fbillstate != "":
        sql_3 = sql_3 + " and fbillstate = :fbillstate "
        param["fbillstate"] = search.fbillstate
    if (search.ftakedateStart != "") and (search.ftakedateEnd != ""):
        sql_3 = sql_3 + " and to_char(ftakedate,'yyyymmdd') between :ftakedateStart and :ftakedateEnd "
        param["ftakedateStart"] = search.ftakedateStart
        param["ftakedateEnd"] = search.ftakedateEnd
    sql_3 = sql_3 + " order by b.fbillno,b.fline"
    data_detail = db.execute(text(sql_3), param).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['fbillno'] = data[key][0]
        d1['fsiteno'] = data[key][1]
        d1['ftakeuser'] = data[key][2]
        d1['ftakedate'] = data[key][3]
        d1['fpointNo'] = data[key][4]
        d1['fitemcount'] = data[key][5]
        d1['fpiececount'] = data[key][6]
        d1['ftotalamt'] = data[key][7]
        d1['fexplanation'] = data[key][8]
        d1['fupddate'] = data[key][9]
        d1['foperator'] = data[key][10]
        d1['fcheckdate'] = data[key][11]
        d1['fcheckuser'] = data[key][12]
        d1['fbillstate'] = data[key][13]
        d1['fbilltype'] = data[key][14]
        sbillno = data[key][0]

        detail_list = []
        for j1 in range(len(data_detail)):
            if (str(data_detail[j1][0]) == str(sbillno)):
                d2 = {}
                d2['fbillno'] = data_detail[j1][0]
                d2['fline'] = data_detail[j1][1]
                d2['fpointsize'] = data_detail[j1][2]
                d2['fequipmentbarcode'] = data_detail[j1][3]
                d2['feuipmentid'] = data_detail[j1][4]
                d2['fmacno'] = data_detail[j1][5]
                d2['fequipmenttag'] = data_detail[j1][6]
                d2['flinkmac'] = data_detail[j1][7]
                d2['flinkmark'] = data_detail[j1][8]
                d2['fbuyprice'] = data_detail[j1][9]
                detail_list.append(d2)

        d1["detailList"] = detail_list
        data_list.append(d1)

    info_dict = {"list": data_list, "limit": search.limit, "page": search.pageIndex,
                 "pages": (count + search.limit - 1) // search.limit,
                 "total": count}
    return BaseResponse.success(data=info_dict)


class EquipmentTakeoutDetail(BaseModel):
    fline: int = Field(..., description="明细行号")
    fpointSize: str = Field(..., description="点位规格")
    fequipmentbarcode: str = Field(..., description="设备条码")
    feuipmentid: int = Field(..., description="设备ID")
    fequipmenttag: str = Field(..., description="设备标签")
    fequipmentname: str = Field(..., description="设备名称")
    fmacno: str = Field(..., description="MAC地址")
    flinkmac: Optional[str] = Field("", description="关联MAC")
    fmarkmark: Optional[str] = Field("", description="关联标识")
    fbuyprice: float = Field(..., description="购买价格")


class EquipmentTakeoutCreate(BaseModel):
    """设备领用新增请求参数"""
    fbillno: str = Field(..., description="单据编号")
    fbillstate: str = Field(..., description="单据状态编码")
    ftakedate: str = Field(..., description="领用日期(yyyy-MM-dd)")
    ftakeuser: str = Field(..., description="领用人")
    foperator: str = Field(..., description="操作人")
    fupddate: str = Field(..., description="更新日期(yyyy-MM-dd HH:mm:ss)")
    fsiteNo: str = Field(..., description="站点编号")
    fpointNo: str = Field(..., description="点位编号")
    fexplanation: Optional[str] = Field("", description="领用说明")
    detailList: List[EquipmentTakeoutDetail] = Field(..., description="领用明细列表")


@take_out_head_router.post("/create", summary="设备领用新增接口")
def create_take_out(create: EquipmentTakeoutCreate, db: Session = Depends(get_db)):
    try:
        fitemcount = 0
        fpiececount = 0
        ftotalamt = 0
        fbillno = create.fbillno
        d1 = {}
        detail_list = []
        with db.begin():
            jsondata = create.detailList
            for data in jsondata:
                sql = "insert into lims.s_tc_takeout_detail(fbillno,fline,fequipmentbarcode,feuipmentid,fpoint_size,fmacno,fequipmenttag,fbuyprice,flinkmac)"
                sql = sql + " values( :fbillno , :fline , :fequipmentbarcode , :feuipmentid , :fpointsize , :fmacno , :fequipmenttag , :fbuyprice , :flinkmac )"
                db.execute(text(sql), {
                    "fbillno": fbillno,
                    "fline": data.fline,
                    "fequipmentbarcode": data.fequipmentbarcode,
                    "feuipmentid": data.feuipmentid,
                    "fpointsize": data.fpointSize,
                    "fmacno": data.fmacno,
                    "fequipmenttag": data.fequipmenttag,
                    "fbuyprice": data.fbuyprice,
                    "flinkmac": data.flinkmac
                })
                fitemcount = fitemcount + 1
                fpiececount = fpiececount + 1

                # 返回明细
                d2 = {}
                d2['fbillno'] = fbillno
                d2['fline'] = data.fline
                d2['fequipmentbarcode'] = data.fequipmentbarcode
                d2['fequipmenttag'] = data.fequipmenttag
                d2['fbuyprice'] = float(data.fbuyprice)
                d2['flinkmac'] = data.flinkmac
                d2['fpointSize'] = data.fpointSize
                d2['fmacno'] = data.fmacno
                detail_list.append(d2)
            sql = "insert into lims.s_tc_takeout_head(fbillno,fsite_no,ftakeuser,ftakedate,fpoint_no,fitemcount,fpiececount,ftotalamt,fexplanation,fupddate,foperator,fbilltype,fbillstate)"
            sql = sql + " values( :fbillno , :fsiteNo , :ftakeuser , :ftakedate , :fpointNo , :fitemcount , :fpiececount ,0, :fexplanation , :fupddate , :ftakeuser ,1, :fbillstate )"
            db.execute(text(sql), {
                "fbillno": fbillno,
                "fsiteNo": create.fsiteNo,
                "ftakeuser": create.ftakeuser,
                "ftakedate": create.ftakedate,
                "fpointNo": create.fpointNo,
                "fitemcount": fitemcount,
                "fpiececount": fpiececount,
                "fexplanation": create.fexplanation,
                "fupddate": create.fupddate,
                "fbillstate": create.fbillstate,
            })

            d1['fbillno'] = fbillno
            d1['fsiteno'] = create.fsiteNo
            d1['ftakedate'] = create.ftakedate
            d1['ftakeuser'] = create.ftakeuser
            d1['fpointNo'] = create.fpointNo
            d1['fitemcount'] = fitemcount
            d1['fpiececount'] = fpiececount
            d1['ftotalamt'] = 0
            d1['ftakedateEnd'] = None
            d1['ftakedateStart'] = None
            d1['fsrcbillno'] = ""
            d1['fexplanation'] = create.fexplanation
            d1['fupddate'] = create.fupddate
            d1['foperator'] = create.foperator
            d1['fcheckdate'] = None
            d1['fcheckuser'] = None
            d1['fbilltype'] = 1
            d1['fbillstate'] = 1
            d1["detailList"] = detail_list
            return BaseResponse.success(data=d1)
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


@take_out_head_router.post("/update", summary="设备领用修改接口")
def update_take_out(update: EquipmentTakeoutCreate, db: Session = Depends(get_db), token: str = Depends(oauth2_scheme)):
    try:
        fitemcount = 0
        fpiececount = 0
        ftotalamt = 0
        fbillno = update.fbillno
        fbillstate = update.fbillstate
        user_id = EncryptionMiddleware.get_current_user(token)
        with db.begin():
            if (fbillstate == 8):
                print("作废")
                sql = "update lims.s_tc_takeout_head "
                sql = sql + " set fbillstate=8,fcheckdate=now(),fcheckuser= :userid "
                sql = sql + " where fbillno= :fbillno "
                db.execute(text(sql), {
                    "fbillno": fbillno,
                    "userid": user_id,
                })
            else:
                sql = "delete from lims.s_tc_takeout_detail where fbillno= :fbillno "
                db.execute(text(sql), {
                    "fbillno": fbillno,
                })

                jsondata = update.detailList
                for data in jsondata:
                    sql = "insert into lims.s_tc_takeout_detail(fbillno,fline,fequipmentbarcode,feuipmentid,fpoint_size,fmacno,fequipmenttag,fbuyprice,flinkmac)"
                    sql = sql + " values( :fbillno , :fline , :fequipmentbarcode , :feuipmentid , :fpointsize , :fmacno , :fequipmenttag , :fbuyprice , :flinkmac )"
                    db.execute(text(sql), {
                        "fbillno": fbillno,
                        "fline": data.fline,
                        "fequipmentbarcode": data.fequipmentbarcode,
                        "feuipmentid": data.feuipmentid,
                        "fpointsize": data.fpointSize,
                        "fmacno": data.fmacno,
                        "fequipmenttag": data.fequipmenttag,
                        "fbuyprice": data.fbuyprice,
                        "flinkmac": data.flinkmac
                    })
                    fitemcount = fitemcount + 1
                    fpiececount = fpiececount + 1
                    ftotalamt = 0
                sql = "update lims.s_tc_takeout_head "
                sql = sql + " set ftakedate= :ftakedate ,ftakeuser= :ftakeuser ,fexplanation= :fexplanation "
                sql = sql + ",fitemcount= :fitemcount ,fpiececount= :fpiececount ,ftotalamt= :ftotalamt "
                sql = sql + ",fupddate=now(),foperator= :userid "
                sql = sql + " where fbillno= :fbillno "
                db.execute(text(sql), {
                    "ftakedate": update.ftakedate,
                    "ftakeuser": update.ftakeuser,
                    "fexplanation": update.fexplanation,
                    "fitemcount": fitemcount,
                    "fpiececount": fpiececount,
                    "ftotalamt": ftotalamt,
                    "userid": user_id,
                    "fbillno": fbillno,
                })
                return BaseResponse.success()
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")
